﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using QAToolsAddin.GenericDialogs;
using QAToolsAddin.GenericDialogs.Fields;
using Excel = Microsoft.Office.Interop.Excel;

namespace QAToolsAddin.UI {
    class Copy {
        private readonly Excel.Application _excelapp;
        private Excel.Range _range;
        private CultureInfo _culture;

        public Copy(Excel.Application application, CultureInfo culture) {
            try {
                _excelapp = application;
                _culture = culture;
                _range = application.ActiveWindow.RangeSelection;
            } catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); }
        }

        public void Columns() {
            var defaultTemplate = new StringBuilder();
            var nbCol = _range.Columns.Count;
            for (int i = 1; i <= nbCol; i++)
                defaultTemplate.Append("\\" + i);

            var f0 = new FieldRadioBT("Use", new string[] { "Delimiter", "Template" }, "Select delimiter or template", 0, true);
            var f1 = new FieldText("Delimiter", "Delimiter to add between columns", "", true);
            var f2 = new FieldText("Template", "Template - \\n is the cell text at column n", defaultTemplate.ToString(), true);
            f0.ValueChanged += (sender, e) => {
                f1.Control.Enabled = f0.Value == 0;
                f2.Control.Enabled = f0.Value == 1;
            };
            f2.Control.Enabled = false;
            new InputBox(_excelapp, "Copy merged columns", f0, f1, f2).Show(delegate {
                bool useDeleimiter = f0.Value == 0;
                var delimiter = f1.Value;
                var pattern = f2.Value;
                try {
                    _excelapp.ScreenUpdating = false;
                    System.Windows.Forms.Clipboard.Clear();
                    var range = _excelapp.Intersect(_range, _range.Worksheet.UsedRange);
                    if (range == null) return;
                    range.Copy();
                    var data = System.Windows.Forms.Clipboard.GetText();
                    _excelapp.CutCopyMode = 0;
                    var res = new StringBuilder();
                    if (useDeleimiter) {
                        res.Append(data.Replace("\t", delimiter));
                    } else {
                        var rows = data.TrimEnd('\r', '\n').Split(new string[] { "\r\n" }, StringSplitOptions.None);
                        foreach (string row in rows) {
                            var cells = row.Split('\t');
                            string strRow = pattern;
                            for (int i = 0; i < cells.Length; i++)
                                strRow = strRow.Replace("\\" + (i + 1), cells[i]);
                            res.Append(strRow + "\r\n");
                        }
                    }
                    System.Windows.Forms.Clipboard.SetText(res.ToString());
                } catch { throw; } finally {
                    _excelapp.ScreenUpdating = true;
                }
            });
        }

        public void CellsText() {
            var f1 = new FieldText("Cells delimiter", "Delimiter character", ";", true);
            var f2 = new FieldCheckBox("Include empty cells", "", false);
            new InputBox(_excelapp, "Copy joined cells text", f1, f2).Show(delegate {
                var cellDelimiter = f1.Value;
                var rowDelimiter = f1.Value;
                var skipEmptyCells = f2.Value == false;
                try {
                    _excelapp.ScreenUpdating = false;
                    System.Windows.Forms.Clipboard.Clear();
                    StringBuilder sb = new StringBuilder();
                    foreach (Excel.Range area in _range.Areas) {
                        var range = _excelapp.Intersect(area, area.Worksheet.UsedRange);
                        if (range == null) break;
                        range.Copy();
                        sb.Append(System.Windows.Forms.Clipboard.GetText());
                    }
                    _excelapp.CutCopyMode = 0;
                    StringBuilder res = new StringBuilder();
                    int len = sb.Length - 2;
                    for (int i = 0; i < len; i++) {
                        if (sb[i] == '\t') {
                            if (skipEmptyCells && (sb[i + 1] == '\t' || (sb[i + 1] == '\r' && sb[i + 2] == '\n')))
                                continue;
                            else
                                res.Append(cellDelimiter);
                        } else if (sb[i] == '\r' && sb[i + 1] == '\n') {
                            i++;
                            if (skipEmptyCells && (sb[i + 2] == '\r' || sb[i + 2] == '\t'))
                                continue;
                            else
                                res.Append(rowDelimiter);
                        } else {
                            res.Append(sb[i]);
                        }
                    }
                    System.Windows.Forms.Clipboard.SetText(res.ToString());
                } catch { throw; } finally {
                    _excelapp.ScreenUpdating = true;
                }
            });
        }

        public void CellsValue() {
            var f1 = new FieldText("String surround", "Surround character for strings", "\"", true);
            var f2 = new FieldText("Number surround", "Surround character for numbers", "", true);
            var f3 = new FieldText("Empty value", "Text for empty values", "\"\"", true);
            var f7 = new FieldText("DateTime format", "DateTime format", "yyyy-MM-ddTHH:mm:ss", true);
            var f4 = new FieldText("Cells delimiter", "Delimiter character for cells", ",", true);
            var f5 = new FieldText("Row surround", "Surround character for rows", "{}", true);
            var f6 = new FieldText("Rows delimiter", "Delimiter character for rows", @",\r\n", true);
            new InputBox(_excelapp, "Copy joined cells value", f1, f2, f3, f7, f4, f5, f6).Show(delegate {
                string stringSurroundLeft, stringSurroundRight, numSurroundLeft, numSurroundRight, rowSurroundLeft, rowSurroundRight;
                ExtractSurround(f1.Value, out stringSurroundLeft, out stringSurroundRight);
                ExtractSurround(f2.Value, out numSurroundLeft, out numSurroundRight);
                ExtractSurround(f5.Value, out rowSurroundLeft, out rowSurroundRight);
                string emptyValue = f3.Value == null ? "" : f3.Value;
                string dtFormat = f7.Value;
                string cellsDelimiter = f4.Value.Replace(@"\r", "\r").Replace(@"\n", "\n").Replace(@"\t", "\t");
                string rowsDelimiter = f6.Value.Replace(@"\r", "\r").Replace(@"\n", "\n").Replace(@"\t", "\t");
                StringBuilder sb = new StringBuilder();
                new CellsOperator(_range, true).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                    if (isFirstCol) {
                        if (sb.Length != 0)
                            sb.Append(rowsDelimiter);
                        sb.Append(rowSurroundLeft);
                    } else
                        sb.Append(cellsDelimiter);

                    if (value == null)
                        sb.Append(emptyValue);
                    else if (value is double)
                        sb.Append(numSurroundLeft + value + numSurroundRight);
                    else if (value is string)
                        sb.Append(stringSurroundLeft + (string)value + stringSurroundRight);
                    else if (value is DateTime)
                        sb.Append(stringSurroundLeft + ((DateTime)value).ToString(dtFormat) + stringSurroundLeft);
                    else
                        sb.Append(stringSurroundLeft + value.ToString() + stringSurroundRight);

                    if (isLastCol)
                        sb.Append(rowSurroundRight);
                });
                if (sb.Length != 0)
                    System.Windows.Forms.Clipboard.SetText(sb.ToString());
            });
        }

        public void ToSQLinsert() {
            Excel.Range dataRange; string[] headers;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var f1 = new FieldText("Table name", "Name of the table", _range.ListObject == null ? "TABLENAME" : _range.ListObject.Name);
            var f2 = new FieldText("Fields", "Fields name separated by a semicolon", string.Join(";", headers));
            var f3 = new FieldText("Null value", "Null expression", "NULL", true);
            var f4 = new FieldText("DateTime", "DateTime format", "yyyy-MM-ddTHH:mm:ss", true);
            var f5 = new FieldRadioBT("Insert type", new[] { "SingleInsert", "MultipleInsert" }, "Type of insert", 0, true);
            new InputBox(_excelapp, "Copy to SQL INSERT", f1, f2, f3, f4, f5).Show(delegate {
                var rows = new List<string>();
                var tablename = f1.Value;
                var columns = f2.Value.Replace(';', ',');
                var nullValue = f3.Value;
                var datetimeformat = f4.Value;
                var singleinsert = f5.Value == 0;
                var multipleinsert = f5.Value == 1;
                const bool unionall = false;

                var row = new StringBuilder();
                new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                    if (isFirstCol == false)
                        row.Append(",");
                    if (value == null)
                        row.Append(nullValue);
                    else if (value is string)
                        row.Append("'" + ((string)value).Replace("'", "''") + "'");
                    else if (value is bool)
                        row.Append((bool)value ? "TRUE" : "FALSE");
                    else if (value is System.DateTime)
                        row.Append("'" + ((System.DateTime)value).ToString(datetimeformat) + "'");
                    else
                        row.Append(value);
                    if (isLastCol) {
                        rows.Add(row.ToString());
                        row.Length = 0;
                    }
                }
                );

                var sql = new StringBuilder();
                if (!string.IsNullOrEmpty(columns))
                    columns = "(" + columns + ")";
                if (singleinsert) {
                    sql.Append("INSERT INTO " + tablename + " VALUES\r\n");
                    var nbRow = rows.Count;
                    for (var r = 0; r < rows.Count; r++) {
                        if (r == (nbRow - 1))
                            sql.Append("(" + rows[r] + ");");
                        else
                            sql.Append("(" + rows[r] + "),\r\n");
                    }
                } else if (multipleinsert) {
                    foreach (var s in rows)
                        sql.Append("INSERT INTO " + tablename + columns + " VALUES(" + s + ");\r\n");
                } else if (unionall) {
                    //sql.Append("INSERT INTO " + tablename + "\r\n");
                    //int nbRow = rows.Count;
                    //for (int r = 0; r < rows.Count; r++) {
                    //    if (r == (nbRow - 1))
                    //        sql.Append("SELECT " + rows[r] + ";");
                    //    else
                    //        sql.Append("SELECT " + rows[r] + "\r\nUNION ALL");
                    //}
                }
                if (sql.Length != 0)
                    System.Windows.Forms.Clipboard.SetText(sql.ToString());
            });
        }

        public void ToSQLdelete() {
            Excel.Range dataRange; string[] headers;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var f1 = new FieldText("Table name", "Name of the table", _range.ListObject == null ? "TABLENAME" : _range.ListObject.Name);
            var f2 = new FieldText("Key fields", "Key fields name separated by a semicolon", string.Join(";", headers));
            f2.NumberOfValues = headers.Length;
            var f3 = new FieldText("Null value", "Null expression", "NULL", true);
            var f4 = new FieldText("DateTime", "DateTime format", "yyyy-MM-ddTHH:mm:ss", true);
            new InputBox(_excelapp, "Copy to SQL DELETE", f1, f2, f3, f4).Show(delegate {
                var tablename = f1.Value;
                var columns = f2.Value.Split(';');
                var nullValue = f3.Value;
                var datetimeformat = f4.Value;
                int colId = 0;
                var sql = new StringBuilder();
                var condition = new StringBuilder();
                new CellsOperator(_range).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                    if (isFirstCol == false)
                        condition.Append(" AND ");
                    condition.Append(columns[colId++] + "=");
                    if (value == null)
                        condition.Append(nullValue);
                    else if (value is string)
                        condition.Append("'" + ((string)value).Replace("'", "''") + "'");
                    else if (value is bool)
                        condition.Append((bool)value ? "TRUE" : "FALSE");
                    else if (value is System.DateTime)
                        condition.Append("'" + ((System.DateTime)value).ToString(datetimeformat) + "'");
                    else
                        condition.Append(value);
                    if (isLastCol) {
                        sql.Append("DELETE FROM " + tablename + " WHERE " + condition + ";\r\n");
                        condition.Length = 0;
                        colId = 0;
                    }
                });
                if (sql.Length != 0)
                    System.Windows.Forms.Clipboard.SetText(sql.ToString());
            });
        }

        public void ToHTML() {
            Excel.Range dataRange; string[] headers;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var html = new StringBuilder();
            html.Append("<table>");
            if (headers.Length != 0) {
                html.Append("\r\n <tr>");
                foreach (var header in headers)
                    html.Append("\r\n  <th>" + header + "</th>");
                html.Append("\r\n </tr>");
            }
            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                if (isFirstCol)
                    html.Append("\r\n <tr>");
                html.Append("\r\n  <td>");
                if (value != null) {
                    if (value is bool)
                        html.Append((bool)value ? "true" : "false");
                    else if (value is System.DateTime)
                        html.Append(((System.DateTime)value).ToString("u"));
                    else
                        html.Append(value.ToString());
                }
                html.Append("</td>");
                if (isLastCol)
                    html.Append("\r\n </tr>");
            });
            html.Append("\r\n</table>");
            System.Windows.Forms.Clipboard.SetText(html.ToString());
        }

        public void ToCSV() {
            Excel.Range dataRange; string[] headers;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var csv = new StringBuilder();
			if(headers.Length != 0){
				foreach (var header in headers) {
					if (csv.Length != 0)
						csv.Append(',');
					csv.Append(header);
				}
                csv.Append("\r\n");
			}
            var specialChars = new char[]{'\r', '\n', '\t', '\"', ','};
            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                if (!isFirstCol)
                    csv.Append(",");
                if (value is string) {
                    if(((string)value).IndexOfAny(specialChars) == -1)
                        csv.Append((string)value);
                    else
                        csv.Append('"' + ((string)value).Replace("\"", "\"\"") +'"');
                } else if (value is bool)
                    csv.Append((bool)value ? "true" : "false");
                else if (value is System.DateTime)
                    csv.Append( ((System.DateTime)value).ToString("u") );
                else if(value != null)
                    csv.Append(value);
                if (isLastCol)
                    csv.Append("\r\n");
            });
            System.Windows.Forms.Clipboard.SetText(csv.ToString().TrimEnd('\r', '\n'));
        }


        public void ToTabular() {
            Excel.Range dataRange; string[] headers;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var sb = new StringBuilder();
            var values = new CellsOperator(dataRange).GetPaddedStringValues(headers);
            int nbRow = values.GetLength(0);
            int nbCol = values.GetLength(1);
            for (int r = 0; r < nbRow; r++) {
                sb.Append("|");
                for (int c = 0; c < nbCol; c++)
                    sb.Append(' ' + values[r, c] + " |");
                sb.Append("\r\n");
            }
            System.Windows.Forms.Clipboard.SetText(sb.ToString());
        }

        public void ToJSON() {
            Excel.Range dataRange; string[] headers; int i = 0;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var json = new StringBuilder();
            json.Append("[");
            var openChar = headers.Length == 0 ? '[' : '{';
            var closeChar = headers.Length == 0 ? ']' : '}';
            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                if (isFirstCol) {
                    if (json.Length != 1)
                        json.Append("," + openChar);
                    else
                        json.Append("\r\n " + openChar);
                    i = 0;
                }
                json.Append("\r\n  ");
                if (i < headers.Length)
                    json.Append("\"" + headers[i++] + "\":");
                if (value == null)
                    json.Append("\"");
                else if (value is string)
                    json.Append("\"" + ((string)value)
                        .Replace("\\", "\\\\")
                        .Replace("\"", "\\\"")
                        .Replace("\r", "\\r")
                        .Replace("\n", "\\n")
                        .Replace("\t", "\\t")
                        + "\"");
                else if (value is bool)
                    json.Append((bool)value ? "true" : "false");
                else if (value is System.DateTime)
                    json.Append("\"" + ((System.DateTime)value).ToString("u") + "\"");
                else
                    json.Append(value);
                if (isLastCol)
                    json.Append("\r\n " + closeChar);
                else
                    json.Append(",");
            }
            );
            json.Append("\r\n]");
            System.Windows.Forms.Clipboard.SetText(json.ToString());
        }

        public void ToXMLnodes() {
            Excel.Range dataRange; string[] headers; int c = 0, r = 0;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var html = new StringBuilder();
            html.Append("<?xml version=\"1.0\" encoding=\"utf-8\" ?>\r\n<Rows>");
            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                if (isFirstCol) {
                    //html.Append("\r\n <Items index=\"" + r + "\">");
                    html.Append("\r\n <Row>");
                    c = 0;
                }
                //html.Append("\r\n  <Item index=\"" + c++ + "\">");
                if (headers.Length == 0)
                    html.Append("\r\n  <Data>");
                else
                    html.Append("\r\n  <Data id=\"" + headers[c++] + "\">");
                if (value != null) {
                    if (value is bool)
                        html.Append((bool)value ? "true" : "false");
                    else if (value is System.DateTime)
                        html.Append(((System.DateTime)value).ToString("u"));
                    else
                        html.Append(value.ToString());
                }
                html.Append("</Data>");
                if (isLastCol) {
                    html.Append("\r\n </Row>");
                    r++;
                }
            });
            html.Append("\r\n</Rows>");
            System.Windows.Forms.Clipboard.SetText(html.ToString());
        }

        public void ToXMLattributes() {
            Excel.Range dataRange; string[] headers; int c = -1;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var html = new StringBuilder();
            html.Append("<?xml version=\"1.0\" encoding=\"utf-8\" ?>\r\n<Rows>");
            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                if (isFirstCol) {
                    html.Append("\r\n  <Row ");
                    c = -1;
                }
                if (++c < headers.Length)
                    html.Append(headers[c]);
                html.Append("=\"");
                if (value != null) {
                    if (value is bool)
                        html.Append((bool)value ? "true" : "false");
                    else if (value is System.DateTime)
                        html.Append(((System.DateTime)value).ToString("u"));
                    else
                        html.Append(value.ToString());
                }
                html.Append("\" ");
                if (isLastCol)
                    html.Append("/>");
            });
            html.Append("\r\n</Rows>");
            System.Windows.Forms.Clipboard.SetText(html.ToString());
        }

        public void ToNUnit() {
            Excel.Range dataRange; string[] headers; int ti = -1;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var unitTest = new StringBuilder();
            string[] types = new string[headers.Length];
            bool readTypes = true;

            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                if (isFirstCol)
                    unitTest.Append("\t\t[TestCase(");
                else
                    unitTest.Append(", ");
                if (readTypes && ++ti < headers.Length) {
                    var type = (value ?? "string").GetType().ToString();
                    types[ti] = type.Substring(type.LastIndexOf('.') + 1);
                }
                if (value == null)
                    unitTest.Append("\"");
                else if (value is string)
                    unitTest.Append("\"" + ((string)value)
                        .Replace("\\", "\\\\")
                        .Replace("\"", "\\\"")
                        .Replace("\r", "\\r")
                        .Replace("\n", "\\n")
                        .Replace("\t", "\\t")
                        + "\"");
                else if (value is bool)
                    unitTest.Append((bool)value ? "true" : "false");
                else if (value is System.DateTime)
                    unitTest.Append("\"" + ((System.DateTime)value).ToString("u") + "\"");
                else if (value is double)
                    unitTest.Append(value.ToString() + "d");
                else
                    unitTest.Append(value);
                if (isLastCol) {
                    unitTest.Append(")]\r\n");
                    readTypes = false;
                }
            }
            );
            unitTest.Append("\t\tpublic void TestCase(");
            for (int i = 0; i < headers.Length; i++) {
                if (i != 0)
                    unitTest.Append(", ");
                unitTest.Append(types[i] + " " + headers[i].ToLower());
            }
            unitTest.Append("){\r\n\r\n\t\t}");
            System.Windows.Forms.Clipboard.SetText(unitTest.ToString().TrimEnd('\r', '\n'));
        }

        public void ToJavaArray() {
            Excel.Range dataRange; string[] headers;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var java = new StringBuilder();

            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
				if (isFirstCol) {
                    if (java.Length == 0) {
                        if (isLastCol) {
                            java.Append("Object[] dataset = {");
                        } else {
                            java.Append("Object[][] dataset = {");
                            if (headers.Length != 0)
                                java.Append("\r\n // " + GetHeaderPosition(headers));
                        }
                    } else {
                        java.Append(",");
                    }
					java.Append(isLastCol ? " " : "\r\n ");
                    if (!isLastCol)
                        java.Append("{");
                } else {
                    java.Append(",");
                }
                if (value == null)
                    java.Append("null");
                else if (value is string)
                    java.Append("\"" + ((string)value)
                        .Replace("\\", "\\\\")
                        .Replace("\"", "\\\"")
                        .Replace("\r", "\\r")
                        .Replace("\n", "\\n")
                        .Replace("\t", "\\t")
                        + "\"");
                else if (value is bool)
                    java.Append((bool)value ? "true" : "false");
                else if (value is System.DateTime)
                    java.Append("\"" + ((System.DateTime)value).ToString("u") + "\"");
                else
                    java.Append(value);
                if (isLastCol && !isFirstCol)
                    java.Append("}");
            });
            var text = java.ToString();
            text += text.IndexOf('\n') == -1 ? " };" : "\r\n};";
            System.Windows.Forms.Clipboard.SetText(text);
        }

        public void ToCSharpArray() {
            Excel.Range dataRange; string[] headers;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var csharp = new StringBuilder();

            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                if (isFirstCol) {
                    if (csharp.Length == 0) {
                        if (isLastCol) {
                            csharp.Append("object[] dataset = {");
                        } else {
                            csharp.Append("object[,] dataset = {");
                            if (headers.Length != 0)
                                csharp.Append("\r\n // " + GetHeaderPosition(headers));
                        }
                    } else {
                        csharp.Append(",");
                    }
                    csharp.Append(isLastCol ? " " : "\r\n ");
                    if (!isLastCol)
                        csharp.Append("{");
                } else {
                    csharp.Append(",");
                }
                if (value == null)
                    csharp.Append("null");
                else if (value is string)
                    csharp.Append("\"" + ((string)value)
                        .Replace("\\", "\\\\")
                        .Replace("\"", "\\\"")
                        .Replace("\r", "\\r")
                        .Replace("\n", "\\n")
                        .Replace("\t", "\\t")
                        + "\"");
                else if (value is bool)
                    csharp.Append((bool)value ? "true" : "false");
                else if (value is System.DateTime)
                    csharp.Append("\"" + ((System.DateTime)value).ToString("u") + "\"");
                else
                    csharp.Append(value);
                if (isLastCol && !isFirstCol)
                    csharp.Append("}");
            });
            var text = csharp.ToString();
            text += text.IndexOf('\n') == -1 ? " };" : "\r\n};";
            System.Windows.Forms.Clipboard.SetText(text);
        }

        public void ToPythonArray() {
            Excel.Range dataRange; string[] headers;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var python = new StringBuilder();

            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                if (isFirstCol) {
                    if (python.Length == 0) {
                        python.Append("dataset = [");
                        if (headers.Length != 0)
                            python.Append("\r\n # " + GetHeaderPosition(headers));
                    } else {
                        python.Append(",");
                    }
                    python.Append("\r\n ");
                    if (!isLastCol)
                        python.Append("[");
                } else {
                    python.Append(",");
                }
                if (value == null)
                    python.Append("\"");
                else if (value is string)
                    python.Append("'" + ((string)value)
                        .Replace("\\", "\\\\")
                        .Replace("\"", "\\\"")
                        .Replace("\r", "\\r")
                        .Replace("\n", "\\n")
                        .Replace("\t", "\\t")
                        + "'");
                else if (value is bool)
                    python.Append((bool)value ? "true" : "false");
                else if (value is System.DateTime)
                    python.Append("'" + ((System.DateTime)value).ToString("u") + "'");
                else
                    python.Append(value);
                if (isLastCol && !isFirstCol)
                    python.Append("]");
            });
            python.Append("\r\n]");
            System.Windows.Forms.Clipboard.SetText(python.ToString());
        }

        public void ToPHPArray() {
            Excel.Range dataRange; string[] headers;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var php = new StringBuilder();

            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                if (isFirstCol) {
                    if (php.Length == 0) {
                        php.Append("$dataset = array(");
                        if (headers.Length != 0)
                            php.Append("\r\n // " + GetHeaderPosition(headers));
                    } else {
                        php.Append(",");
                    }
                    php.Append("\r\n ");
                    if (!isLastCol)
                        php.Append("array(");
                } else {
                    php.Append(",");
                }
                if (value == null)
                    php.Append("\"");
                else if (value is string)
                    php.Append("\"" + ((string)value)
                        .Replace("\\", "\\\\")
                        .Replace("\"", "\\\"")
                        .Replace("\r", "\\r")
                        .Replace("\n", "\\n")
                        .Replace("\t", "\\t")
                        + "\"");
                else if (value is bool)
                    php.Append((bool)value ? "true" : "false");
                else if (value is System.DateTime)
                    php.Append("\"" + ((System.DateTime)value).ToString("u") + "\"");
                else
                    php.Append(value);
                if (isLastCol && !isFirstCol)
                    php.Append(")");
            });
            php.Append("\r\n);");
            System.Windows.Forms.Clipboard.SetText(php.ToString());
        }

        public void ToVbArray() {
            Excel.Range dataRange; string[] headers; int r = 0;
            GetDataRangeAndHeaders(_range, out dataRange, out headers);
            var vbData = new StringBuilder();
            new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {

                if (isFirstCol) {
                    if (isLastCol)
                        vbData.Append("\r\ndata(" + r + ") = ");
                    else
                        //vbData.Append("\r\ndata(" + r + "," + c++ + ") = ");
                        vbData.Append("\r\ndata(" + r + ") = Array(");
                } else {
                    vbData.Append(",");
                }

                if (value == null)
                    vbData.Append("\"");
                else if (value is string)
                    vbData.Append("\"" + ((string)value)
                        .Replace("\\", "\\\\")
                        .Replace("\"", "\"\"")
                        .Replace("\r", "\\r")
                        .Replace("\n", "\\n")
                        .Replace("\t", "\\t")
                        + "\"");
                else if (value is bool)
                    vbData.Append((bool)value ? "true" : "false");
                else if (value is System.DateTime)
                    vbData.Append("\"" + ((System.DateTime)value).ToString("u") + "\"");
                else
                    vbData.Append(value);
                if (isLastCol) {
                    if (!isFirstCol)
                        vbData.Append(")");
                    r++;
                }
            });
            var vb = new StringBuilder();
            vb.Append("Dim data(" + r + ")");

            if (headers.Length != 0)
                vb.Append("\r\n' " + GetHeaderPosition(headers));
            vb.Append(vbData);
            System.Windows.Forms.Clipboard.SetText(vb.ToString());
        }

        private string GetHeaderPosition(string[] headers) {
            var sb = new StringBuilder();
            int i = 0;
            foreach (var header in headers) {
                if (sb.Length != 0)
                    sb.Append(" ");
                sb.Append(i++.ToString() + ":" + header);
            }
            return sb.ToString();
        }

        private void GetDataRangeAndHeaders(Excel.Range range, out Excel.Range dataRange, out string[] headers) {
            dataRange = null;
            headers = new string[] { };
            var titles = new List<string>();
            if (range == null) return;
            Excel.Range headerRange;
            if (range.ListObject != null) {
                headerRange = _excelapp.Intersect(range.EntireColumn, range.ListObject.HeaderRowRange);
                dataRange = _excelapp.Intersect(range, range.ListObject.DataBodyRange);
            } else if ((bool)((Excel.Range)range.Rows[1]).Font.Bold) {
                headerRange = (Excel.Range)range.Rows[1];
                dataRange = range.Resize[range.Rows.Count - 1, Type.Missing].Offset[1, Type.Missing];
            } else {
                headerRange = null;
                dataRange = range;
            }
            if (headerRange != null) {
                new CellsOperator(headerRange, true).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
                    titles.Add(value.ToString());
                });
            }
            headers = titles.ToArray();
        }

        private void ExtractSurround(string value, out string left, out string rigth) {
            if (value.Length > 1) {
                left = value.Substring(0, value.Length / 2);
                rigth = value.Substring(value.Length / 2);
            } else {
                left = value;
                rigth = value;
            }
        }
    }
}
